using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Alf.Site.Entidades;

namespace MakeIT.Alf.Site.Negocios
{
	public partial class AlfDAO : DAO
	{

		public static AreacomumVO getAreacomum(int? idAreaComum, string areaComum)
		{
			AreacomumVO areacomumVO = null;
			List<AreacomumVO> listAreacomumVOs = getAreacomums(idAreaComum, areaComum);
			if (listAreacomumVOs != null && listAreacomumVOs.Count > 0)
			{
				areacomumVO = listAreacomumVOs[0];
			}
			return areacomumVO;
		}

		public static AreacomumVO getAreacomum(int? idAreaComum)
		{
			return getAreacomum(idAreaComum, null);
		}

		public static List<AreacomumVO> getAreacomums()
		{
			return getAreacomums(null, null);
		}

		public static List<AreacomumVO> getAreacomums(int? idAreaComum)
		{
			return getAreacomums(idAreaComum, null);
		}

		public static List<AreacomumVO> getAreacomums(int? idAreaComum, string areaComum)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT arc.idAreaComum as arc_idAreaComum, arc.areaComum as arc_areaComum, arc.Cadastro as arc_Cadastro, arc.CadastradoPor as arc_CadastradoPor, arc.Edicao as arc_Edicao, arc.EditadoPor as arc_EditadoPor  ");
			query.AppendLine("FROM areacomum as arc ");
			if (idAreaComum != null)
			{
				query.AppendLine(where + "arc.idAreaComum=@idAreaComum ");
				where = "AND ";
			}
			if (areaComum != null)
			{
				query.AppendLine(where + "arc.areaComum LIKE @areaComum ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY arc.areaComum ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idAreaComum!= null) cmd.Parameters.Add(getParameter(cmd, "@idAreaComum", idAreaComum));
			if (areaComum!= null) cmd.Parameters.Add(getParameter(cmd, "@areaComum", "%"+areaComum+"%"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<AreacomumVO> listAreacomum = new List<AreacomumVO>();

				while (reader.Read())
				{
					AreacomumVO areacomumVO = AreacomumVO.CreateVO();
					areacomumVO.idAreaComum = reader.GetInt32(reader.GetOrdinal("arc_idAreaComum"));
					areacomumVO.areaComum = reader.GetString(reader.GetOrdinal("arc_areaComum"));
					areacomumVO.cadastro = reader.GetDateTime(reader.GetOrdinal("arc_Cadastro"));
					areacomumVO.cadastradoPor = reader.GetString(reader.GetOrdinal("arc_CadastradoPor"));
					areacomumVO.edicao = reader.GetDateTime(reader.GetOrdinal("arc_Edicao"));
					areacomumVO.editadoPor = reader.GetString(reader.GetOrdinal("arc_EditadoPor"));


					listAreacomum.Add(areacomumVO);
				}
				reader.Close();
				return listAreacomum;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
			}
		}

		public static void setAreacomum(AreacomumVO areacomumVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE areacomum ");
			query.AppendLine("SET areaComum=@areaComum,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idAreaComum=@idAreaComum ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idAreaComum", areacomumVO.idAreaComum));
			cmd.Parameters.Add(getParameter(cmd, "@areaComum", areacomumVO.areaComum));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", areacomumVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", areacomumVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", areacomumVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", areacomumVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public static long newAreacomum(AreacomumVO areacomumVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO areacomum ");
			query.AppendLine("  ( areaComum, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @areaComum, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@areaComum", areacomumVO.areaComum));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", areacomumVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", areacomumVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", areacomumVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", areacomumVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('areacomum') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void delAreacomum(AreacomumVO areacomumVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM areacomum ");
			query.AppendLine("WHERE idAreaComum=@idAreaComum ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idAreaComum", areacomumVO.idAreaComum));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}
	}
}
